﻿/*----------------------------------------------------------------
// Copyright (C) 2010
// 版权所有
//
// 文件名：DbHelperSQL.cs
// 文件功能描述：
//
//
// 创建标识：Libing 2010-02-02 16:53:36 星期二
//
// 修改标识：
// 修改描述：
//----------------------------------------------------------------*/

using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;

namespace WMS.DBUtility
{
    public abstract class DbHelperSQL
    {
        #region 字段

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private static readonly string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["WMSSQLServer"].ConnectionString;

        public static string ConStr
        {
            get { return connectionString; }
        }

        #endregion

        #region  执行简单SQL语句

        public static int ExecuteSqlStringNonQuery(string cmdText)
        {
            return ExecuteNonQuery(cmdText, CommandType.Text, null);
        }

        public static object ExecuteSqlStringScalar(string cmdText)
        {
            return ExecuteScalar(cmdText, CommandType.Text, null);
        }

        public static SqlDataReader ExecuteSqlStringReader(string cmdText)
        {
            return ExecuteReader(cmdText, CommandType.Text, null);
        }

        public static DataSet ExecuteSqlStringDataSet(string cmdText)
        {
            return ExecuteDataSet(cmdText, CommandType.Text, null);
        }

        public static DataSet ExecuteSqlStringPagedDataSet(string cmdText, int startIndex, int pageSize)
        {
            return ExecutePagedDataSet(cmdText, CommandType.Text, startIndex, pageSize, null);
        }

        #endregion

        #region 执行带参数的SQL语句

        public static int ExecuteSqlStringNonQuery(string cmdText, params SqlParameter[] parameters)
        {
            return ExecuteNonQuery(cmdText, CommandType.Text, parameters);
        }

        public static object ExecuteSqlStringScalar(string cmdText, params SqlParameter[] parameters)
        {
            return ExecuteScalar(cmdText, CommandType.Text, parameters);
        }

        public static SqlDataReader ExecuteSqlStringReader(string cmdText, params SqlParameter[] parameters)
        {
            return ExecuteReader(cmdText, CommandType.Text, parameters);
        }

        public static DataSet ExecuteSqlStringDataSet(string cmdText, params SqlParameter[] parameters)
        {
            return ExecuteDataSet(cmdText, CommandType.Text, parameters);
        }

        public static DataSet ExecuteSqlStringPagedDataSet(string cmdText, int startIndex, int pageSize, params SqlParameter[] parameters)
        {
            return ExecutePagedDataSet(cmdText, CommandType.Text, startIndex, pageSize, parameters);
        }

        #endregion

        #region 存储过程操作

        public static int ExecuteStoredProcNonQuery(string cmdText, params SqlParameter[] parameters)
        {
            return ExecuteNonQuery(cmdText, CommandType.StoredProcedure, parameters);
        }

        public static int ExecuteStoredProcNonQuery(SqlCommand cmd)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }

                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.StoredProcedure;

                    int rows = cmd.ExecuteNonQuery();

                    if (cmd.Parameters.Count > 0)
                    {
                        cmd.Parameters.Clear();
                    }

                    return rows;
                }
                catch (SqlException ex)
                {
                    throw new Exception(ex.Message, ex);
                }
            }
        }

        public static object ExecuteStoredProcScalar(string cmdText, params SqlParameter[] parameters)
        {
            return ExecuteScalar(cmdText, CommandType.StoredProcedure, parameters);
        }

        public static SqlDataReader ExecuteStoredProcReader(string cmdText, params SqlParameter[] parameters)
        {
            return ExecuteReader(cmdText, CommandType.StoredProcedure, parameters);
        }

        public static DataSet ExecuteStoredProcDataSet(string cmdText, params SqlParameter[] parameters)
        {
            return ExecuteDataSet(cmdText, CommandType.StoredProcedure, parameters);
        }

        public static DataSet ExecuteStoredProcDataSet(string cmdText, int startIndex, int pageSize, params SqlParameter[] parameters)
        {
            return ExecutePagedDataSet(cmdText, CommandType.StoredProcedure, startIndex, pageSize, parameters);
        }

        #endregion

        #region 公共方法

        /// <summary>
        /// 执行Sql语句,返回是否存在.
        /// </summary>
        /// <param name="cmdText">Sql语句</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        private static bool Exists(string cmdText, CommandType cmdType, params SqlParameter[] parameters)
        {
            object obj = ExecuteScalar(cmdText, cmdType, parameters);

            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
            {
                return false;
            }
            else
            {
                return Convert.ToInt32(obj.ToString()) > 0;
            }
        }

        /// <summary>
        /// 执行Sql语句,返回影响的记录数.
        /// </summary>
        /// <param name="cmdText">Sql语句</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        private static int ExecuteNonQuery(string cmdText, CommandType cmdType, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, conn, null, cmdType, cmdText, parameters);

                        int rows = cmd.ExecuteNonQuery();

                        if (cmd.Parameters.Count > 0)
                        {
                            cmd.Parameters.Clear();
                        }

                        return rows;
                    }
                    catch (SqlException ex)
                    {
                        throw new Exception(ex.Message, ex);
                    }
                }
            }
        }

        /// <summary>
        /// 执行Sql语句,返回查询结果(Object).
        /// </summary>
        /// <param name="cmdText">Sql语句</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        private static object ExecuteScalar(string cmdText, CommandType cmdType, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, conn, null, cmdType, cmdText, parameters);

                        object result = cmd.ExecuteScalar();

                        if (cmd.Parameters.Count > 0)
                        {
                            cmd.Parameters.Clear();
                        }

                        return result;
                    }
                    catch (SqlException ex)
                    {
                        throw new Exception(ex.Message, ex);
                    }
                }
            }
        }

        /// <summary>
        /// 执行Sql语句,返回 SqlDataReader.
        /// </summary>
        /// <param name="cmdText">Sql语句</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        private static SqlDataReader ExecuteReader(string cmdText, CommandType cmdType, params SqlParameter[] parameters)
        {
            SqlConnection conn = new SqlConnection(connectionString);
            SqlCommand cmd = new SqlCommand();

            try
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, parameters);

                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

                if (cmd.Parameters.Count > 0)
                {
                    cmd.Parameters.Clear();
                }

                return dr;
            }
            catch (SqlException ex)
            {
                conn.Close();

                throw new Exception(ex.Message, ex);
            }
        }

        /// <summary>
        /// 执行Sql语句,返回 DataSet.
        /// </summary>
        /// <param name="cmdText">Sql语句</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        private static DataSet ExecuteDataSet(string cmdText, CommandType cmdType, params SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, cmdType, cmdText, parameters);

                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        DataSet ds = new DataSet();
                        da.Fill(ds);

                        if (cmd.Parameters.Count > 0)
                        {
                            cmd.Parameters.Clear();
                        }

                        return ds;
                    }
                    catch (SqlException ex)
                    {
                        throw new Exception(ex.Message, ex);
                    }
                }
            }
        }

        /// <summary>
        /// 执行Sql语句,返回分页 DataSet.
        /// </summary>
        /// <param name="cmdText">Sql语句</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="startIndex">开始索引</param>
        /// <param name="pageSize">分页大小</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        private static DataSet ExecutePagedDataSet(string cmdText, CommandType cmdType, int startIndex, int pageSize, params SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(cmd, connection, null, cmdType, cmdText, parameters);

                        SqlDataAdapter da = new SqlDataAdapter(cmd);
                        DataSet ds = new DataSet();
                        da.Fill(ds, startIndex, pageSize, "ds");

                        if (cmd.Parameters.Count > 0)
                        {
                            cmd.Parameters.Clear();
                        }

                        return ds;
                    }
                    catch (SqlException ex)
                    {
                        throw new Exception(ex.Message, ex);
                    }
                }
            }
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] parameters)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }

            cmd.Connection = conn;
            cmd.CommandType = cmdType;
            cmd.CommandText = cmdText;

            if (trans != null)
            {
                cmd.Transaction = trans;
            }

            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        #endregion
    }
}
